package com.hnqcgczy.supplier.dao.impl;

import com.hnqcgczy.supplier.dao.EvaluateDao;
import com.hnqcgczy.supplier.db.MyTransaction;
import com.hnqcgczy.supplier.utils.BaseDao;
import com.hnqcgczy.supplier.vo.Evaluate;
import com.hnqcgczy.supplier.vo.EvaluateVo;

import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

public class EvaluateDaoImpl extends BaseDao implements EvaluateDao {
    public EvaluateDaoImpl(MyTransaction myTransaction) {
        super(myTransaction);
    }

    @Override
    public int add(Evaluate evaluate) throws SQLException {
        //1.准备sql
        String sql = "INSERT INTO `t_evaluate`(`evaluate_id`, `company_id`, `" +
                "admin_name`, `evaluate_infor`, `score`, `order_id`, `date`) " +
                "VALUES (null, ?, ?, ?, ?, ?, ?)";
        //2.调公共方法update
        int i = super.update(sql,
                evaluate.getCompanyId(),
                evaluate.getAdminName(),
                evaluate.getEvaluateInfor(),
                evaluate.getScore(),
                evaluate.getOrderId(),
                evaluate.getDate());

        //3.关闭资源
        closeResource();
        return i;
    }

    @Override
    public int update(Evaluate evaluate) throws SQLException {
        //1.准备sql
        String sql = "UPDATE `t_evaluate` SET `company_id` = ?, `admin_name` = ?, " +
                "`evaluate_infor` = ?, `score` = ?, `order_id` = ?, " +
                "`date` = ? WHERE `evaluate_id` = ?";
        //2.调公共方法update
        int i = super.update(sql,
                evaluate.getCompanyId(),
                evaluate.getAdminName(),
                evaluate.getEvaluateInfor(),
                evaluate.getScore(),
                evaluate.getOrderId(),
                evaluate.getDate(),
                evaluate.getEvaluateId()
        );
        //3.关闭资源
        closeResource();
        return i;
    }

    @Override
    public int delete(Integer evaluateId) throws SQLException {
        //1.准备sql
        String sql = "DELETE FROM t_evaluate where evaluate_id = ?";
        //2.调公共方法update
        int i = super.update(sql, evaluateId);
        //3.关闭资源
        closeResource();
        return i;
    }

    @Override
    public EvaluateVo findOne(Integer evaluateId) throws SQLException {
        String sql = "select evaluate_id,t2.company_id tid,company_short_name,\n" +
                "admin_name,evaluate_infor,score,order_id,date from t_evaluate t1 \n" +
                "   inner join t_company t2\n" +
                "\t    on t1.company_id = t2.company_id  where evaluate_id=?";
        ResultSet rs = super.query(sql, evaluateId);
        EvaluateVo evaluateVo = new EvaluateVo();

        if (rs != null) {
            while (rs.next()) {
                //从数据库中查出数据填充到实体
                evaluateVo.setEvaluateId(rs.getInt("evaluate_id"));
                evaluateVo.setCompanyId(rs.getInt("tid"));
                evaluateVo.setCompanyName(rs.getString("company_short_name"));
                evaluateVo.setAdminName(rs.getString("admin_name"));
                evaluateVo.setEvaluateInfor(rs.getString("evaluate_infor"));
                evaluateVo.setScore(rs.getDouble("score"));
                evaluateVo.setOrderId(rs.getInt("order_id"));
                evaluateVo.setDate(rs.getDate("date"));

            }
        }
        closeResource();
        return evaluateVo;
    }

    @Override
    public List<EvaluateVo> findByLike(EvaluateVo evaluateVo, int start, int size) throws SQLException {
        StringBuffer msg = new StringBuffer("select evaluate_id,t2.company_id tid,company_short_name,\n" +
                "admin_name,evaluate_infor,score,order_id,date from t_evaluate t1 \n" +
                "   inner join t_company t2\n" +
                "\t    on t1.company_id = t2.company_id  where 1=1");
        //建一个集合
        List<Object> parameterList = new ArrayList<Object>();
        if (evaluateVo != null) {

            //供应商名称,servlet处理：如果为空，空字符串，就给个0
            if (!"空".equals(evaluateVo.getCompanyName())) {
                msg.append(" and t2.company_short_name = ?");
                parameterList.add(evaluateVo.getCompanyName());
            }
            //评价人名称，servlet处理，给它一个初始值
            if (!"空".equals(evaluateVo.getAdminName())) {
                msg.append(" and t1.admin_name = ? ");
                parameterList.add(evaluateVo.getAdminName());
            }
            //评价日期
            if (!"1970-01-01".equals(evaluateVo.getDate() + "")) {
                msg.append(" and t1.date like ? ");
                parameterList.add("%" + evaluateVo.getDate() + "%");
            }
            //合同编号,servlet处理：如果为空，空字符串，就给个0
            if (0 != evaluateVo.getOrderId()) {
                msg.append(" and t1.order_id = ? ");
                parameterList.add(evaluateVo.getOrderId());
            }

        }
        //分页呢
        msg.append(" limit ").append(start).append(",").append(size);
        String sql = msg.toString();
        System.out.println(sql);
        //调查询的公共方法
        ResultSet rs = super.query(sql, parameterList.toArray());
        List<EvaluateVo> lists = null;
        if (rs != null) {
            lists = new ArrayList<EvaluateVo>();
            while (rs.next()) {
                //从数据库中查出数据填充到实体
                EvaluateVo temp = new EvaluateVo();
                temp.setEvaluateId(rs.getInt("evaluate_id"));
                temp.setCompanyId(rs.getInt("tid"));
                temp.setCompanyName(rs.getString("company_short_name"));
                temp.setAdminName(rs.getString("admin_name"));
                temp.setEvaluateInfor(rs.getString("evaluate_infor"));
                temp.setScore(rs.getDouble("score"));
                temp.setOrderId(rs.getInt("order_id"));
                temp.setDate(rs.getDate("date"));
                lists.add(temp);
                temp = null;
            }
        }
        closeResource();
        //这里尤其注意，不要忘记。
        return lists;
    }

    @Override
    public int totals(EvaluateVo evaluateVo) throws SQLException {

        StringBuffer msg = new StringBuffer("select count(*) totals from t_evaluate t1 \n" +
                "   inner join t_company t2\n" +
                "\t    on t1.company_id = t2.company_id  where 1=1");
        //建一个集合
        List<Object> parameterList = new ArrayList<Object>();
        if (evaluateVo != null) {

            //供应商名称,servlet处理：如果为空，空字符串，就给个0
            if (!"空".equals(evaluateVo.getCompanyName())) {
                msg.append(" and t2.company_short_name = ?");
                parameterList.add(evaluateVo.getCompanyName());
            }
            //评价人名称，servlet处理，给它一个初始值
            if (!"空".equals(evaluateVo.getAdminName())) {
                msg.append(" and t1.admin_name = ? ");
                parameterList.add(evaluateVo.getAdminName());
            }
            //评价日期
            if (!"1970-01-01".equals(evaluateVo.getDate() + "")) {
                msg.append(" and t1.date like ? ");
                parameterList.add("%" + evaluateVo.getDate() + "%");
            }
            //合同编号,servlet处理：如果为空，空字符串，就给个0
            if (0 != evaluateVo.getOrderId()) {
                msg.append(" and t1.order_id = ? ");
                parameterList.add(evaluateVo.getOrderId());
            }

        }
        System.out.println(msg.toString());
        ResultSet rs = super.query(msg.toString(), parameterList.toArray());
        int totals = 0;
        if (rs != null) {
            while (rs.next()) {
                totals = rs.getInt("totals");
            }
        }
        closeResource();
        return totals;
    }
}
